﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CvModel;
using System.Data.Entity.Migrations;
using System.Data.SqlClient;
using System.Data;


namespace DAL
{
    public class WorkAdDB
    {
        private static CvModelContainer _context = new CvModelContainer();

        //CREATE
        public static int CreateWorkAd(WorkAd workAd)
        {
            try
            {
                var query = _context.WorkAd.Add(workAd);
                return _context.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }
        //READ
        public static List<WorkAd> GetAllWorkAds()
        {
            try
            {
                var allWorkAds =
                    (from a in _context.WorkAd
                        where a.IsDeleted == false
                        select a).ToList();
                foreach (var ad in allWorkAds)
                {
                    if (ad.DateAdded < DateTime.Now.AddMonths(-2) || ad.DayOfApplication < DateTime.Now.AddDays(-14))
                    {
                        ad.IsDeleted = true;
                    }
                }
                _context.SaveChanges();
                var allWorkAdsSorted =
                    (from a in _context.WorkAd
                     where a.IsDeleted == false
                     select a).ToList();
                return allWorkAdsSorted;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static List<WorkAd> GetAllDeletedAdsFromAdminId(int adminId)
        {
            try
            {
                var allWorkAds =
                    (from a in _context.WorkAd
                     where a.IsDeleted == true && a.AdminId == adminId
                     select a).ToList();
                return allWorkAds;
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        public static List<WorkAd> GetAllAdsFromAdminId(int adminId)
        {
            try
            {
                var allWorkAds =
                    (from a in _context.WorkAd
                     where a.IsDeleted == false && a.AdminId == adminId
                     select a).ToList();
                return allWorkAds;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static List<WorkAd> GetAllAdsFromApplicantId(int applicantId)
        {
            try
            {
                var allWorkAdsFromApplicant =
                    (from a in _context.WorkAd
                     from w in _context.WorkApplications
                     where a.IsDeleted == false && w.ApplicantId == applicantId && a.WorkAdId == w.WorkAdId
                     select a).ToList();
                return allWorkAdsFromApplicant;
            }
            catch (Exception)
            {

                throw;
            }
        }
        public static ContactPerson GetContactPerson(int workAdId)
        {
            try
            {
            var contactPerson =
                (from w in _context.WorkAd
                 from a in _context.Admin
                 from c in _context.ContactPerson
                 where w.WorkAdId == workAdId && w.AdminId == a.AdminId && c.WorkAdminId == a.AdminId
                 select c).First();
            return contactPerson;
            }
            catch (Exception)
            {
                
                throw;
            }

        }
        public static WorkAd GetWorkAd(int workAdId)
        {
            try
            {
                var workAd =
                (from w in _context.WorkAd
                 where w.WorkAdId == workAdId
                 select w).First();
                return workAd;
            }
            catch (Exception)
            {

                throw;
            }
        }
        //UPDATE
        public static int UpdateWorkAd(WorkAd workAd,int workAdId)
        {
            try
            {
                var query =
                    (from w in _context.WorkAd
                        where w.WorkAdId == workAdId
                        select w).SingleOrDefault();
                query.City = workAd.City;
                query.ContactPersonContactPersonId = workAd.ContactPersonContactPersonId;
                query.DateAdded = workAd.DateAdded;
                query.DayOfApplication = workAd.DayOfApplication;
                query.Duration = workAd.Duration;
                query.IsDeleted = workAd.IsDeleted;
                query.Proffession = workAd.Proffession;
                query.Qualifications = workAd.Qualifications;
                query.Salary = workAd.Salary;
                query.WorkDescription = workAd.WorkDescription;
                query.AdminId = workAd.AdminId;
                
                return _context.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }
        //DELETE
        public static int DeleteWorkAd(int workAdId)
        {
            try
            {
                var query =
                    (from w in _context.WorkAd
                     where w.WorkAdId == workAdId
                     select w).Single().IsDeleted = true;

                //_context.WorkAd.Remove(query);

                return _context.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
